/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nxlv2.nomad.accessdb.dao.jdbc;

import com.nxlv2.nomad.accessdb.dao.DAOException;
import com.nxlv2.nomad.accessdb.dao.ICommandeDao;
import com.nxlv2.nomad.accessdb.dao.IncoherentResultException;
import com.nxlv2.nomad.accessdb.dao.NoResultException;
import com.nxlv2.nomad.businessobjects.Adresse;
import com.nxlv2.nomad.businessobjects.Commande;
import com.nxlv2.nomad.businessobjects.DetailCommande;
import com.nxlv2.nomad.businessobjects.EtatCommande;
import com.nxlv2.nomad.businessobjects.Satisfaction;
import com.nxlv2.nomad.businessobjects.TauxSatisfaction;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Nastasia
 */
public class JdbcCommandeDao implements ICommandeDao {

    private static final Logger logger = LoggerFactory.getLogger(JdbcCommandeDao.class);
    private static final ConnectionManager manager = new ConnectionManager();

    /**
     *
     * @param commande
     * @throws DAOException
     */
    @Override
    public void saveCommande(Commande commande) throws DAOException {

        if (null == commande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }
        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO commande (idBase, date, numCommande, etatCommande, idAdresseFacturation, idAdresseLivraison, dateCreation) ");
        builder.append("VALUES ( ?, ?, ?, AES_ENCRYPT(?,?), ?, ?, NOW())");

        String request = builder.toString();
        logger.debug("Insert commande: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request, PreparedStatement.RETURN_GENERATED_KEYS);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        logger.debug("Commande {}", commande);
        logger.debug("Libelle {}", commande.getLibelleEtatCommande());
        System.out.println(commande.getIdBase() + ", " + new java.sql.Date(commande.getDate().getTime()) + ", " + commande.getLibelleEtatCommande()
                + ", " + manager.getKeyAES() + ", " + commande.getIdAdresseFacturation() + ", " + commande.getIdAdresseLivraison());

        try {
            ps.setInt(1, commande.getIdBase());
            ps.setDate(2, new java.sql.Date(commande.getDate().getTime()));
            ps.setLong(3, commande.getNumCommande());
            ps.setString(4, commande.getLibelleEtatCommande());
            ps.setString(5, manager.getKeyAES());
            ps.setInt(6, commande.getIdAdresseFacturation());
            ps.setInt(7, commande.getIdAdresseLivraison());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.", e);
            throw new DAOException("Can not populate request with data.", e);
        }

        try {
            ResultSet rs = ps.getGeneratedKeys();
            int pk = 0;

            while (rs.next()) {
                pk = rs.getInt(1);
            }
            commande.setIdAppli(pk);
        } catch (SQLException e) {
            logger.error("Can not execute insert in db.");
            throw new DAOException("Can not execute insert in db.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param id
     * @throws DAOException
     */
    @Override
    public void deleteCommande(int id) throws DAOException {
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("DELETE FROM commande WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Delete commande: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not delete commande with id.", e);
            throw new DAOException("Can not delete commande with id.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param commande
     * @throws DAOException
     */
    @Override
    public void updateCommande(Commande commande) throws DAOException {

        if (null == commande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }
        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE commande ");
        builder.append("SET idBase = ?, "
                + "date = ?, "
                + "etatCommande = AES_ENCRYPT(?,?), "
                + "idAdresseFacturation = ?, "
                + "idAdresseLivraison = ?, "
                + "dateModification = NOW() ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Update commande: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, commande.getIdBase());
            ps.setDate(2, new java.sql.Date(commande.getDate().getTime()));
            ps.setString(3, commande.getLibelleEtatCommande());
            ps.setString(4, manager.getKeyAES());
            ps.setInt(5, commande.getIdAdresseFacturation());
            ps.setInt(6, commande.getIdAdresseLivraison());
            ps.setInt(7, commande.getIdAppli());
            logger.debug("Update commande: ps {}", ps);
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param id
     * @return Commande
     * @throws DAOException
     */
    @Override
    public Commande findCommandeById(int id) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT commande.idBase, commande.idAppli, "
                + "AES_DECRYPT(commande.etatCommande,?) AS etatCommande, "
                + "commande.numCommande, "
                + "commande.idAdresseFacturation, "
                + "commande.idAdresseLivraison, "
                + "commande.date ");
        builder.append("FROM commande "
                + "WHERE commande.idAppli = ? ");

        String request = builder.toString();
        logger.debug("select commande: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setString(1, manager.getKeyAES());
            ps.setInt(2, id);
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Commande> commandes = null;
        try {
            commandes = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == commandes || commandes.isEmpty()) {
            throw new NoResultException("No commande found with id " + id);
        }
        if (commandes.size() > 1) {
            throw new IncoherentResultException("Many records with a same id.");
        }
        manager.fermerConnection();
        return commandes.get(0);
    }

    /**
     *
     * @param rst
     * @return List<Commande>
     * @throws SQLException
     */
    private List<Commande> map(ResultSet rst) throws SQLException {
        List<Commande> commandes = new ArrayList<>();

        while (rst.next()) {

            Commande commande = new Commande();
            commande.setIdAppli(rst.getInt("idAppli"));
            commande.setIdBase(rst.getInt("idBase"));
            commande.setNumCommande(rst.getLong("numCommande"));
            commande.setEtatCommande(EtatCommande.getByLibelle(rst.getString("etatCommande")));
            commande.setIdAdresseFacturation(rst.getInt("idAdresseFacturation"));
            commande.setIdAdresseLivraison(rst.getInt("idAdresseLivraison"));
            commande.setDate(rst.getDate("date"));
            commandes.add(commande);
        }
        return commandes;
    }

    /**
     *
     * @param commande
     * @return List<DetailCommande>
     * @throws DAOException
     */
    @Override
    public List<DetailCommande> getDetailCommandesParCommande(Commande commande) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli , idBase, quantite ");
        builder.append("FROM detailcommande WHERE idAppliCommande = ?");

        String request = builder.toString();
        logger.debug("Select detailCommande par commande: {}", request);
        logger.debug("commande {}", commande);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, commande.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<DetailCommande> detailsCommande = null;
        try {
            detailsCommande = mapDetail(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == detailsCommande || detailsCommande.isEmpty()) {
            throw new NoResultException("No detailCommande found with id commande " + commande.getIdAppli());
        }
manager.fermerConnection();
        return detailsCommande;
    }
    /**
     *
     * @param commande
     * @return List<DetailCommande>
     * @throws DAOException
     */
    @Override
    public List<DetailCommande> getDetailCommandesMajParCommande(Commande commande) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT dc.idAppli , dc.idBase, dc.quantite ");
        builder.append("FROM detailcommande dc "
                + "JOIN commande co ON co.idAppli = dc.idAppliCommande "
                + "JOIN client cl ON cl.idAppli = co.idAppliClient "
                + "JOIN utilisateur u ON u.idAppli = cl.idAppliUtilisateur "
                + "WHERE dc.idAppliCommande = ? "
                + "AND (dc.idBase = 0 OR dc.dateModification > u.dateSynchro)");

        String request = builder.toString();
        logger.debug("Select detailCommande par commande: {}", request);
        logger.debug("commande {}", commande);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, commande.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<DetailCommande> detailsCommande = null;
        try {
            detailsCommande = mapDetail(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

//        if (null == detailsCommande || detailsCommande.isEmpty()) {
//            throw new NoResultException("No detailCommande found with id commande " + commande.getIdAppli());
//        }
manager.fermerConnection();
        return detailsCommande;
    }

    /**
     *
     * @param rst
     * @return List<DetailCommande>
     * @throws SQLException
     */
    private List<DetailCommande> mapDetail(ResultSet rst) throws SQLException {
        List<DetailCommande> detailsCommande = new ArrayList<>();

        while (rst.next()) {
            DetailCommande detailCommande = new DetailCommande();
            detailCommande.setIdAppli(rst.getInt("idAppli"));
            detailCommande.setIdBase(rst.getInt("idBase"));
            detailCommande.setQuantite(rst.getInt("quantite"));
            detailsCommande.add(detailCommande);
        }
        return detailsCommande;
    }

    /**
     *
     * @param detailCommande
     * @param commande
     * @throws DAOException
     */
    @Override
    public void lierDetailCommandeACommande(DetailCommande detailCommande, Commande commande) throws DAOException {

        if (null == commande || null == detailCommande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE detailCommande ");
        builder.append("SET idAppliCommande = ?,");
        builder.append("dateModification = NOW()");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Lier detailCommande à commande: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, commande.getIdAppli());
            ps.setInt(2, detailCommande.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not link detailCommande to commande.", e);
            throw new DAOException("Can not link detailCommande to commande.", e);
        }
manager.fermerConnection();
    }

    /**
     *
     * @param rst
     * @return
     * @throws SQLException
     */
    private List<Satisfaction> mapSatisfaction(ResultSet rst) throws SQLException {
        List<Satisfaction> satisfactions = new ArrayList<>();

        while (rst.next()) {
            Satisfaction satisfaction = new Satisfaction();
            satisfaction.setIdAppli(rst.getInt("idAppli"));
            satisfaction.setIdBase(rst.getInt("idBase"));
            satisfaction.setTauxSatisfaction(TauxSatisfaction.getByValeur(rst.getString("tauxSatisfaction")));
            satisfaction.setMessage(rst.getString("message"));
            satisfaction.setDate(rst.getDate("date"));
            satisfactions.add(satisfaction);
        }
        return satisfactions;
    }

    /**
     *
     * @param satisfaction
     * @param commande
     * @throws DAOException
     */
    @Override
    public void lierSatisfactionACommande(Satisfaction satisfaction, Commande commande) throws DAOException {

        if (null == commande || null == satisfaction) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE satisfaction ");
        builder.append("SET idAppliCommande = ?,");
        builder.append("dateModification = NOW()");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Lier satisfaction à commande: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, commande.getIdAppli());
            ps.setInt(2, satisfaction.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not link satisfaction to commande.", e);
            throw new DAOException("Can not link satisfaction to commande.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param commande
     * @return List<Satisfaction>
     * @throws DAOException
     */
    @Override
    public List<Satisfaction> getSatisfactionParCommande(Commande commande) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli , idBase,"
                + " AES_DECRYPT(tauxSatisfaction,?) AS tauxSatisfaction,"
                + " AES_DECRYPT(message,?) AS message, date ");
        builder.append("FROM satisfaction WHERE idAppliCommande = ?");

        String request = builder.toString();
        logger.debug("Select satifaction par commande: {}", request);
        logger.debug("commande {}", commande);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setInt(3, commande.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Satisfaction> satisfactions = null;
        try {
            satisfactions = mapSatisfaction(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

//        if (null == satisfactions || satisfactions.isEmpty()) {
//            throw new NoResultException("No detailCommande found with id commande " + commande.getIdAppli());
//        }
manager.fermerConnection();
        return satisfactions;
    }
}
